#!/usr/bin python3.6
# -*- encoding: utf-8 -*-
"""
@File    : __init__.py.py
@Description : 爬取京东商品数据
@Author  : tong.li
@Email   : lt_alex@163.com
@Blog    : https://ltalex.gitee.io
@Time    : 2020/12/6 下午8:31
"""
import datetime
from decimal import Decimal

import requests
from pyquery import PyQuery as pq
import time
from requests.exceptions import RequestException
import re
from elasticsearch import Elasticsearch
from elasticsearch import helpers
import json
import random
import pymysql

# HTTP请求头
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36',
    'cookie': ''
}
# 静态IP代理池，数据来源自快代理:https://free.kuaidaili.com/free/
proxy = {
    'http': 'http://120.220.220.95:8085'
}

# 关键字信息，相当于京东搜索输入框
keywords = "墨镜"

# 存储到MySQL
db = pymysql.connect(host='127.0.0.1',
                     port=3306,
                     user='test',
                     password='123456',
                     database='test')
# 创建游标对象 cursor
cursor = db.cursor()

# 存储到:MySQL
"""
表结构定义如下
CREATE TABLE t_goods (
  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  sku varchar(20) NOT NULL DEFAULT '' COMMENT '商品SKU编码',
  title varchar(150) NOT NULL DEFAULT '' COMMENT '商品标题',
  imgUrl varchar(150) NOT NULL DEFAULT '' COMMENT '商品主图片',
  price int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品价格，单位：分',
  status tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '商品状态，0-下架，1-上架',
  shopName varchar(100) NOT NULL DEFAULT '' COMMENT '店铺名称',
  evaluationCount int(10) unsigned NOT NULL DEFAULT '0' COMMENT '平均数',
  transactionsCount int(10) unsigned NOT NULL DEFAULT '0' COMMENT '成交数',
  detailUrl varchar(150) NOT NULL DEFAULT '' COMMENT '商品详情跳转地址',
  category varchar(10) NOT NULL DEFAULT '' COMMENT '商品分类',
  createTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updateTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
"""


def storeToMySQL(datas):
    try:
        # 当前时间
        now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        subSqls = []
        for data in datas:
            # 状态0-1随机
            subSqls.append("( '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}' )"
                           .format(data['id'], data['title'], data['imgUrl'], data['price'], random.getrandbits(1),
                                   data['shopName'],
                                   data['evaluationCount'], data['transactionsCount'], data['detailUrl'], keywords, now,
                                   now))
        sql = "{0} {1};".format(
            'INSERT INTO t_goods(sku, title, imgUrl, price, status, shopName, evaluationCount, transactionsCount, detailUrl, category, createTime, updateTime) VALUES ',
            ', '.join(subSqls))
        print(sql)
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        db.rollback()
        print("数据插入失败")


# 连接ElasticSearch
# es = Elasticsearch(
#     ['127.0.0.1'],
#     port=9200
# )

# 存储到:ElasticSearch
# def storeToES(datas):
#     actions = []
#     for data in datas:
#         action = {
#             '_index': 'jd_goods',  # 索引名称
#             '_source': data
#         }
#         actions.append(action)
#     helpers.bulk(es, actions)


def request_page(url):
    try:

        datas = []
        response = requests.get(url, proxies=proxy, headers=headers)
        if response.status_code == 200:
            text = response.text
            # 获取京东商品数据
            doc = pq(text)
            lis = doc('div#J_goodsList li[data-sku]').items()
            for index, li in enumerate(lis):
                goods = {
                    # ID=微秒级时间戳+索引
                    'id': int(round(time.time() * 1000000)) + index,
                    # SKU
                    'sku': li.attr('data-sku'),
                    #  名称,正则替换掉不需要的空格
                    'title': re.sub('\\s{2,}|\\n*', '', li('.p-name em').text()),
                    #  图片
                    'imgUrl': 'https:' + li('.p-img img').attr('data-lazy-img'),
                    # 价格
                    'price': Decimal(0.0 if li('.p-price i').text() == '免费' else li('.p-price i').text()) * Decimal(
                        "100"),
                    # 店铺名称
                    'shopName': li('.p-shop span').text(),
                    # 评价数,随机生成
                    'evaluationCount': random.randint(100, 99999),
                    # 成交数,随机生成
                    'transactionsCount': random.randint(100, 9999),
                    # 跳转到的商品详情地址
                    'detailUrl': 'https:' + li('.p-img a').attr('href')
                }
                datas.append(goods)
            return datas
        return None
    except RequestException:
        return None


def getData(url, pageIndex):
    datas = request_page(url)
    # 往ES/MySQL批量插入数据
    if datas is not None and len(datas) != 0: {
        # storeToES(datas)
        storeToMySQL(datas)
    }
    print(json.dumps(datas, cls=DecimalEncoder, ensure_ascii=False))
    print(f'第{pageIndex}页爬取完成' + '-' * 200)


# JSON序列化Decimal的支持类
class DecimalEncoder(json.JSONEncoder):
    def default(self, o):
        if isinstance(o, Decimal):
            return float(o)
        super(DecimalEncoder, self).default(o)


if __name__ == '__main__':
    # # 爬取京东商品数据
    url = 'https://search.jd.com/Search?keyword=' + keywords + '&enc=utf-8&page='
    for i in range(100):
        getData(url + str(i + 1), str(i + 1))
        # 京东有反爬虫限制,爬的太多会有IP或验证码限制,等待0.5毫秒再次请求
        time.sleep(0.7)
